package db_template

import (
	"errors"
	"fmt"
	"log"
)

type User struct {
	UserId   int    `db:"user_id"`
	Username string `db:"username"`
	Sex      string `db:"sex"`
	Email    string `db:"email"`
}

func UserQuery(id int) (*User, error) {
	rows, err := DB.Query("select * from user where user_id=? limit 1", id)
	if err != nil {
		log.Println("查询出现错误:", err)
		return nil, errors.New(err.Error())
	}
	user := new(User)
	for rows.Next() {
		if err := rows.Scan(&user.UserId, &user.Username, &user.Sex, &user.Email); err != nil {
			log.Println("scan error:", err)
			return nil, errors.New(err.Error())
		}
	}
	return user, nil
}

func UserSave() {
	r, err := DB.Exec("insert into user (username,sex,email) values(?,?,?)", "mszlu001", "man", "001@mszlu.com")
	if err != nil {
		log.Println("执行sql语句出错")
		panic(any(err))
	}
	id, err := r.LastInsertId()
	if err != nil {
		panic(any(err))
	}
	fmt.Println("插入成功:", id)
}

func UserUpdate(username string, id int) {
	ret, err := DB.Exec("update user set username=? where user_id=?", username, id)
	if err != nil {
		log.Println("更新出现问题:", err)
		return
	}
	affected, _ := ret.RowsAffected()
	fmt.Println("更新成功的行数:", affected)
}

func UserDelete(id int) {
	ret, err := DB.Exec("delete from user where user_id=?", id)
	if err != nil {
		log.Println("删除出现问题:", err)
		return
	}
	affected, _ := ret.RowsAffected()
	fmt.Println("删除成功的行数:", affected)
}

// UserInsertTx 事务 Transaction
func UserInsertTx(username string) {
	tx, err := DB.Begin()
	if err != nil {
		log.Println("开启事务错误:", err)
		return
	}
	ret, err := tx.Exec("insert into user (username,sex,email) values (?,?,?)", username, "man", "test@mszlu.com")
	if err != nil {
		log.Println("事务sql执行出错:", err)
		return
	}
	id, _ := ret.LastInsertId()
	fmt.Println("插入成功:", id)
	if username == "lisi" {
		fmt.Println("回滚...")
		_ = tx.Rollback()
	} else {
		_ = tx.Commit()
	}

}
